package com.baseweb.webadmin.core.model.Sql;

import com.baseweb.webadmin.core.model.bvo.*;
import com.baseweb.webadmin.core.model.pojo.ByChannel;
import com.baseweb.webadmin.core.model.pojo.ByUserOrder;
import com.baseweb.webadmin.core.model.pojo.User;
import com.baseweb.webadmin.core.util.RequestPage;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;

/**
 * Created by zx on 2017/7/13.
 */
public class SqlProvider {

    private Logger logger = LoggerFactory.getLogger(SqlProvider.class);
    public SqlProvider() {
    }

    /**
     * 查询所有订单总金额及总定金
     * @param para
     * @return
     */
    public String sumPriceFromOrder(Map<String, Object> para){
        String sql = selectOrderPageSql(para);
        return "select sum(price) as price ,sum(deposit) as deposit from (" + sql.split("LIMIT")[0] + ") temp";
    }

    /**
     * 订单分页
     * @param para
     * @return
     */
    public String selectOrderPageSql(Map<String, Object> para){
        StringBuffer sql = new StringBuffer();
        sql.append("select o.refundDesn,o.user_stars,o.id,u.cn_name,u.mobile,bu.cn_name as bu_cn_name,bu.mobile as bu_mobile,c.`name` as channel_name,o.pay_type,o.price" +
                ",o.deposit,o.`status`,o.pay_status,o.create_time,o.descn,bu.city,bu.area,bu.province,gu.cn_name as agent from by_user_order o join `user` bu on bu.id = o.biz_user_id " +
                "join by_channel c on c.id = bu.biz_work_type join `user` u on u.id = o.user_id left join user gu on bu.biz_agent_id = gu.id where 1 = 1");

        if(null != para.get("userId")){
            sql.append(" AND o.user_id = " + para.get("userId"));
        }
        if(null != para.get("bizUserId")){
            sql.append(" AND o.biz_user_id = " + para.get("bizUserId"));
        }
        if(null != para.get("guserId")){
            sql.append(" AND gu.id = " + para.get("guserId"));
        }
        if(null != para.get("status")){
            if("-3".equals(para.get("status").toString())){//查询所有退款流程的订单
                sql.append(" AND (o.status = 4 or o.status = 7 or o.status = 8)");
            }else if("6".equals(para.get("status").toString())){//查询所有退款流程的订单
                sql.append(" AND (o.status = 6 or o.status = 9)");
            }else if("20".equals(para.get("status").toString())){//待接单
                sql.append(" AND (o.status = 2 or o.status = 5)");
            }else{
                sql.append(" AND o.status = " + para.get("status"));
            }
        }
        if(null != para.get("payStatus")){
            if("-2".equals(para.get("payStatus").toString())){
                sql.append(" AND (o.pay_status = 1 or o.pay_status = 2) ");
            }else if("-1".equals(para.get("payStatus").toString())){
                sql.append(" AND o.status != 3 AND o.pay_status = " + para.get("payStatus"));
            }else{
                sql.append(" AND o.pay_status = " + para.get("payStatus"));
            }
        }
        if(null != para.get("professionName")){
            sql.append(" AND c.name = '" + para.get("professionName") + "'");
        }
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        if(null != para.get("startTime")){
            sql.append(" AND o.create_time >= '" + para.get("startTime") + "'");
        }
        if(null != para.get("endTime")){
            sql.append(" AND o.create_time <= '" + para.get("endTime") + "'");
        }
        if(para.get("area") != null && !"".equals(para.get("area").toString()) && !"区域".equals(para.get("area").toString())){
            if(para.get("area").toString().indexOf(",") != -1){
                sql.append(" AND bu.area in (" + para.get("area").toString() + ")");
            }else{

                sql.append(" AND bu.area = '" + para.get("area").toString() + "'");
            }
        }
        if(null != para.get("order")){
            ByUserOrderInfoBvo order = (ByUserOrderInfoBvo)para.get("order");
            if(null != order.getUserStars()){
                sql.append(" AND o.user_stars is null AND o.`status` = 6" );
            }
            if(order.getId() != null){
                sql.append(" AND o.id = " + order.getId());
            }
//            if(order.getStatus() != null){
//                sql.append(" AND o.`status` = " + order.getStatus());
//            }
            if(order.getPayType() != null){
                sql.append(" AND o.pay_type = " + order.getPayType());
            }
            if(order.getUser() != null){
                User user = order.getUser();
                if(user.getArea() != null && !"区域".equals(user.getArea())){
                    sql.append(" AND bu.area = '" + user.getArea() + "'");
                }
                if(user.getCity() != null && !"城市".equals(user.getCity())){
                    sql.append(" AND bu.city = '" + user.getCity() + "'");
                }
                if(user.getProvince() != null && !"省份".equals(user.getProvince())){
                    sql.append(" AND bu.province = '" + user.getProvince() + "'");
                }
                if(null != user.getCnName()){
                    sql.append(" AND u.cn_name like '%" + user.getCnName() + "%'");
                }
                if(null != user.getMobile1()){
                    sql.append(" AND u.mobile like '%" + user.getMobile1() + "%'");
                }

            }
            if(order.getBizUser() != null){
                UserInfoBvo bizUser = order.getBizUser();
                if(null != bizUser.getCnName()){
                    sql.append(" AND bu.cn_name like '%" + bizUser.getCnName() + "%'");
                }
                if(null != bizUser.getMobile()){
                    sql.append(" AND bu.mobile like '%" + bizUser.getMobile() + "%'");
                }
            }
        }
        sql.append( " order by id desc" + " LIMIT " + para.get("start") + "," + para.get("offset"));
        return sql.toString();
    }

    public String selectOrderCountSql(Map<String, Object> para){
        StringBuffer sql = new StringBuffer();
        sql.append("select o.id,u.cn_name,u.mobile,bu.cn_name as bu_cn_name,bu.mobile as bu_mobile,c.`name` as channel_name,o.pay_type,o.price" +
                ",o.deposit,o.`status`,o.pay_status,o.create_time,o.descn,gu.cn_name as agent from by_user_order o  join `user` bu on bu.id = o.biz_user_id " +
                " join by_channel c on c.id = bu.biz_work_type  join `user` u on u.id = o.user_id left join user gu on bu.biz_agent_id = gu.id where 1 = 1");

        if(null != para.get("userId")){
            sql.append(" AND o.user_id = " + para.get("userId"));
        }
        if(null != para.get("bizUserId")){
            sql.append(" AND o.biz_user_id = " + para.get("bizUserId"));
        }
        if(null != para.get("guserId")){
            sql.append(" AND gu.id = " + para.get("guserId"));
        }
        if(null != para.get("status")){
            if("-3".equals(para.get("status").toString())){//查询所有退款流程的订单
                sql.append(" AND (o.status = 4 or o.status = 7 or o.status = 8)");
            }else{
                sql.append(" AND o.status = " + para.get("status"));
            }
        }
        if(null != para.get("payStatus")){
            if("-2".equals(para.get("payStatus").toString())){
                sql.append(" AND (o.pay_status = 1 or o.pay_status = 2) ");
            }else if("-1".equals(para.get("payStatus").toString())){
                sql.append(" AND o.status != 3 AND o.pay_status = " + para.get("payStatus"));
            }else{
                sql.append(" AND o.pay_status = " + para.get("payStatus"));
            }
        }
        if(null != para.get("professionName")){
            sql.append(" AND c.name = '" + para.get("professionName") + "'");
        }
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        if(null != para.get("startTime")){
            sql.append(" AND o.create_time >= '" + para.get("startTime") + "'");
        }
        if(null != para.get("endTime")){
            sql.append(" AND o.create_time <= '" + para.get("endTime") + "'");
        }
        if(para.get("area") != null && !"".equals(para.get("area").toString()) && !"区域".equals(para.get("area"))){
            if(para.get("area").toString().indexOf(",") != -1){
                sql.append(" AND bu.area in (" + para.get("area").toString() + ")");
            }else{

                sql.append(" AND bu.area = '" + para.get("area").toString() + "'");
            }
        }
        if(null != para.get("order")){
            ByUserOrderInfoBvo order = (ByUserOrderInfoBvo)para.get("order");
            if(null != order.getUserStars()){
                sql.append(" AND o.user_stars is null AND o.`status` = 6" );
            }
            if(order.getId() != null){
                sql.append(" AND o.id = " + order.getId());
            }
//            if(order.getStatus() != null){
//                sql.append(" AND o.`status` = " + order.getStatus());
//            }
            if(order.getPayType() != null){
                sql.append(" AND o.pay_type = " + order.getPayType());
            }
            if(order.getUser() != null){
                User user = order.getUser();
                if(user.getArea() != null && !"区域".equals(user.getArea())){
                    sql.append(" AND bu.area = '" + user.getArea() + "'");
                }
                if(user.getCity() != null && !"城市".equals(user.getCity())){
                    sql.append(" AND bu.city = '" + user.getCity() + "'");
                }
                if(user.getProvince() != null && !"省份".equals(user.getProvince())){
                    sql.append(" AND bu.province = '" + user.getProvince() + "'");
                }
                if(null != user.getCnName()){
                    sql.append(" AND u.cn_name like '%" + user.getCnName() + "%'");
                }
                if(null != user.getMobile()){
                    sql.append(" AND u.mobile like '%" + user.getMobile() + "%'");
                }

            }
            if(order.getBizUser() != null){
                UserInfoBvo bizUser = order.getBizUser();
                if(null != bizUser.getCnName()){
                    sql.append(" AND bu.cn_name like '%" + bizUser.getCnName() + "%'");
                }
                if(null != bizUser.getMobile()){
                    sql.append(" AND bu.mobile like '%" + bizUser.getMobile() + "%'");
                }
            }
        }
        String sqlStr = sql.toString();
        return "select count(*) from (" + sqlStr + ") temp";
    }

    /**
     * 订单详情分页
     * @param para
     * @return
     */
    public String selectOrderDetailPageSql(Map<String, Object> para){
        StringBuffer sql = new StringBuffer();
        sql.append("select o.id,u.cn_name,bu.cn_name as bu_cn_name,bu.avatar,p.`name` as product_name,o.price,o.deposit,o.schedule_date,o.dinner_type,o.amount from by_user_order_detail o " +
                " join `user` bu on bu.id = o.biz_user_id  join `user` u on u.id = o.user_id  join by_user_biz_product p on p.id = o.product_id where 1 = 1 ");

        if(null == para.get("orderId")){
            sql.append(" AND o.user_id = " + para.get("userId"));
        }else{
            sql.append(" AND o.order_id = " + para.get("orderId"));
        }
        sql.append(" order by id desc");
        if(null != para.get("start")){
            sql.append(" LIMIT " + para.get("start")+ ","+ para.get("offset"));
        }
        return sql.toString();
    }

    /**
     * 报价列表
     * @param param
     * @return
     */
    public String selectProductPageSql(Map<String, Object> param){
        StringBuffer sql = new StringBuffer();
        sql.append("select b.id, u.cn_name, u.mobile, c.`name` as channel_name, b.`name`, b.current_price, u.address, b.unit, b.`status`, " +
                "b.deposit, b.audit_count, b.product_type, b.update_time, u.id userId " +
                "from by_user_biz_product b " +
                "left join user u on u.id = b.user_id " +
                "left join by_channel c on c.id = u.biz_work_type " +
                "where b.status <> 2 ");

        sql.append(productWhere(param));
        sql.append(" order by id desc" + " LIMIT " + param.get("start")+ ","+ param.get("offset"));
        return sql.toString();
    }

    private String productWhere(Map<String, Object> param) {
        StringBuffer sql = new StringBuffer("");
        if(null != param.get("userId")){
            sql.append(" AND b.user_id = " + param.get("userId"));
        }
        if (param.get("userName") != null && !param.get("userName").equals("")) {
            sql.append(" AND u.cn_name LIKE '%" + param.get("userName") + "%'");
        }
        if (param.get("userMobile") != null && !param.get("userMobile").equals("")) {
            sql.append(" AND u.mobile = '" + param.get("userMobile") + "'");
        }
        if (param.get("userChannel") != null && !param.get("userChannel").equals("")) {
            sql.append(" AND c.name = '" + param.get("userChannel") + "'");
        }
        if (param.get("keyword") != null && !param.get("keyword").equals("")) {
            sql.append(" AND b.name LIKE '%" + param.get("keyword") + "%'");
        }
        if (param.get("auditType") != null && Integer.valueOf(param.get("auditType").toString()) != 0) {
            if (Integer.valueOf(param.get("auditType").toString()) == -1) {
                sql.append(" AND (b.product_type is null OR b.product_type = -1)");
            } else {
                sql.append(" AND b.product_type = " + param.get("auditType"));
            }
        }
        if (param.get("status") != null && Integer.valueOf(param.get("status").toString()) != 0) {
            sql.append(" AND b.status = " + param.get("status"));
        }
        if (param.get("startTime") != null && !param.get("startTime").equals("")) {
            sql.append(" AND b.update_time >= '" + param.get("startTime") + "'");
        }
        if (param.get("endTime") != null && !param.get("endTime").equals("")) {
            sql.append(" AND b.update_time <= '" + param.get("endTime") + "'");
        }
        if (param.get("province") != null && !param.get("province").equals("")) {
            sql.append(" AND u.province = '" + param.get("province") + "'");
        }
        if (param.get("city") != null && !param.get("city").equals("")) {
            sql.append(" AND u.city = '" + param.get("city") + "'");
        }
        if (param.get("county") != null && !param.get("county").equals("")) {
            sql.append(" AND u.area in(" + param.get("county") + ")");
        }
        return sql.toString();
    }

    public String selectProductCountSql(Map<String, Object> param) {
        StringBuffer sql = new StringBuffer();
        sql.append("select count(1) " +
                "from by_user_biz_product b " +
                "left join user u on u.id = b.user_id " +
                "left join by_channel c on c.id = u.biz_work_type " +
                "where b.status <> 2 ");

        sql.append(productWhere(param));
        return sql.toString();
    }

    /**
     * 我的作品列表
     * @param para
     * @return
     */
    public String selectUserExamplePageSql(Map<String, Object> para){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT e.view_count,e.create_time,e.update_time,u.id as userId,u.biz_cover,u.cn_name,u.mobile,u.area,u.city,u.province,c.`name` as channelName,e.id,e.`name`,e.img_url" +
                ",e.`status`,e.case_time,e.example_type,e.case_detail,e.descn,e.case_hotel,e.case_comment,e.case_type,e.case_environment from by_user_example e  join user u on e.user_id = u.id" +
                "  join by_channel c on c.id = u.biz_work_type where e.status != -2");

        int pageStart = 1;
        int pageOffset = 10;
        if(null != para.get("page")){
            RequestPage page = (RequestPage)para.get("page");
            pageStart = page.getStart();
            pageOffset = page.getPageSize();
        }
        ByUserExampleBvo exampleBvo = null;
        if(null != para.get("exampleBvo")){
            exampleBvo = (ByUserExampleBvo)para.get("exampleBvo");
            if("case".equals(exampleBvo.getDescn())){//案例
                sql = new StringBuffer();
                sql.append("select * from (");
                sql.append("(SELECT e.view_count,e.create_time,u.cn_name,u.mobile,u.area,u.city,u.province,c.`name` as channelName,e.id,e.`name`,e.img_url" +
                        ",e.`status`,e.case_time,e.example_type,e.case_detail,e.descn,e.case_hotel,e.case_comment,e.case_type,e.case_environment," +
                        "(select count(*) from by_example_discuss d where d.example_id = e.id) as totalD,(select count(*) from by_example_discuss d where d.example_id = e.id and d.score > 3) as goodD " +
                        "  from by_user_example e  join user u on e.user_id = u.id join by_channel c on c.id = u.biz_work_type where (e.status = 2 or e.`status` = 5) and e.example_type = 3 and u.user_type != 3 and u.user_type != 4 order by e.id desc)");
                sql.append(" union ");
                sql.append("(SELECT e.view_count,e.create_time,u.cn_name,u.mobile,u.area,u.city,u.province,c.`name` as channelName,e.id,e.`name`,e.img_url" +
                        ",e.`status`,e.case_time,e.example_type,e.case_detail,e.descn,e.case_hotel,e.case_comment,e.case_type,e.case_environment," +
                        "(select count(*) from by_example_discuss d where d.example_id = e.id) as totalD,(select count(*) from by_example_discuss d where d.example_id = e.id and d.score > 3) as goodD " +
                        "  from by_user_example e  join user u on e.user_id = u.id join by_channel c on c.id = u.biz_work_type where e.status = 5 and e.example_type = 3 and (u.user_type = 3 or u.user_type = 4) order by e.id desc)" );
                sql.append(") e where 1 = 1 ");

            }else if("detail".equals(exampleBvo.getDescn())){//商家详情中的作品
                sql = new StringBuffer();
                sql.append("SELECT e.view_count,e.create_time,u.cn_name,u.mobile,u.area,u.city,u.province,c.`name` as channelName,e.id,e.`name`,e.img_url" +
                        ",e.`status`,e.case_time,e.example_type,e.case_detail,e.descn,e.case_hotel,e.case_comment,e.case_type,e.case_environment," +
                        "(select count(*) from by_example_discuss d where d.example_id = e.id) as totalD,(select count(*) from by_example_discuss d where d.example_id = e.id and d.score > 3) as goodD " +
                        "  from by_user_example e  join user u on e.user_id = u.id join by_channel c on c.id = u.biz_work_type where (e.`status` = 5 or e.`status` = 2 )");
            }
            if(null != exampleBvo.getExampleType()){
                sql.append(" AND e.example_type = " + exampleBvo.getExampleType() );
            }
            if(exampleBvo.getStatus() != null){
                if(exampleBvo.getStatus() == -100){
                    sql.append(" AND e.`status` != 0");
                }else{
                    sql.append(" AND e.`status` = " + exampleBvo.getStatus());
                }
            }
            if(null != exampleBvo.getExampleName()){
                sql.append(" AND e.name like '%" + exampleBvo.getExampleName() + "%'");
            }
            if(null != exampleBvo.getName()){//模拟开始时间
                sql.append(" AND e.create_time >= '" + exampleBvo.getName() + "'");
            }
            if(null != exampleBvo.getCaseComment()){//模拟结束时间
                sql.append(" AND e.create_time <= '" + exampleBvo.getCaseComment() + " 23:59:59'");
            }
            if(null != exampleBvo.getId()){
                sql.append(" AND e.id = " + exampleBvo.getId());
            }
            if (exampleBvo.getCaseType() != null && !"不限".equals(exampleBvo.getCaseType())) {
                sql.append(" AND e.case_type = '" + exampleBvo.getCaseType() + "'");
            }
            if (exampleBvo.getCaseEnvironment() != null && !"不限".equals(exampleBvo.getCaseEnvironment())) {
                sql.append(" AND e.case_environment = '" + exampleBvo.getCaseEnvironment() + "'");
            }
            if (exampleBvo.getCaseDetail() != null && !"不限".equals(exampleBvo.getCaseDetail())) {
                String _caseDetail = exampleBvo.getCaseDetail();
                int _caseDetailIndex = _caseDetail.indexOf("-");
                if (_caseDetailIndex != -1) {
                    String[] _caseDetailArr = _caseDetail.split("-");
                    sql.append(" AND e.case_detail >= " + _caseDetailArr[0] + " AND e.case_detail <= " + _caseDetailArr[1].split("元")[0]);
                } else {
                    if (_caseDetail.indexOf("50000") != -1) {
                        sql.append(" AND e.case_detail > 50000");
                    } else {
                        sql.append(" AND e.case_detail < 5000");
                    }
                }
            }
            if(exampleBvo.getUserInfoBvo() != null){
                UserInfoBvo user = exampleBvo.getUserInfoBvo();
                if(user.getId() != null){
                    sql.append(" AND u.id = '" + user.getId() + "'");
                }
                if(user.getArea() != null &&  !"".equals(user.getArea()) &&  !"区域".equals(user.getArea())){
                    if(user.getArea().indexOf(",") != -1){
                        sql.append(" AND u.area in (" + user.getArea() + ")");
                    }else{

                        sql.append(" AND u.area = '" + user.getArea() + "'");
                    }
                }
                if("case".equals(exampleBvo.getDescn())){
                    if(user.getCity() != null && !"城市".equals(user.getCity())){
                        sql.append(" AND e.city = '" + user.getCity() + "'");
                    }
                }else {
                    if(user.getCity() != null && !"城市".equals(user.getCity())){
                        sql.append(" AND u.city = '" + user.getCity() + "'");
                    }
                }

                if(user.getProvince() != null && !"省份".equals(user.getProvince())){
                    sql.append(" AND u.province = '" + user.getProvince() + "'");
                }
                if(null != user.getCnName()){
                    sql.append(" AND u.cn_name like '%" + user.getCnName() + "%'");
                }
                if(null != user.getMobile()){
                    sql.append(" AND u.mobile like '%" + user.getMobile() + "%'");
                }

                if(user.getBizWork() != null){
                    ByChannel byChannel = user.getBizWork();
                    if(null != byChannel.getName()){
                        sql.append(" AND c.name = '" + byChannel.getName() + "'");
                    }
                }
            }
        }
        if(exampleBvo != null && !"case".equals(exampleBvo.getDescn())){
            sql.append( " order by e.id desc" + " LIMIT " + pageStart + "," + pageOffset);
        }else{
            if(exampleBvo.getOrderDesn() != null){
                sql.append(" order by " + exampleBvo.getOrderDesn());
            }
            sql.append( " LIMIT " + pageStart + "," + pageOffset);
        }
        logger.debug(sql.toString());
        return sql.toString();
    }

    /**
     * 我的作品总行数
     * @param para
     * @return
     */
    public String countUserExamplePageSql(Map<String, Object> para){
        String sql = selectUserExamplePageSql(para);
        return "select count(*) from (" + sql.split("LIMIT")[0] + ") temp";
    }

    /**
     * 评论管理分页
     * @param params
     * @return
     */
    public String selectOrderEvaluatSql(Map<String, Object> params) {
        Integer findFlag = 1;
        int pageStart = 1;
        int pageOffset = 10;
        StringBuffer sql = new StringBuffer();
        if(params.containsKey("userOrder") && params.get("userOrder") != null) {
            ByUserOrder userOrder = (ByUserOrder) params.get("userOrder");
            findFlag = userOrder.getFindFlag();
        }
        if(findFlag == 1){
            sql.append("SELECT r.id,r.evaluation_time,custom.biz_cover,custom.cn_name as customName,business.cn_name as businessName,r.user_comment as customCommont," +
                    "custom.province,custom.city,custom.area district, r.user_stars as customStar,detail.schedule_date ");
        }else if(findFlag == 2){
            sql.append("SELECT count(*) ");
        }
        sql.append("from by_user_order r, `user` custom, `user` business,by_user_order_detail detail " +
                "where detail.order_id = r.id and r.user_id=custom.id and r.biz_user_id=business.id and (r.`status`=6 or r.`status`=9) ");
        if(params.containsKey("userOrder") && params.get("userOrder") != null){
            if(findFlag ==1) {
                if(null != params.get("start")){
                    pageStart = (int)params.get("start");
                }
                if(null != params.get("offset")){
                    pageOffset = (int)params.get("offset");
                }
            }
            ByUserOrder userOrder = (ByUserOrder) params.get("userOrder");
            if(userOrder.getDistrict() != null && !"区域".equals(userOrder.getDistrict())){
                sql.append(" AND custom.area = '" + userOrder.getDistrict() + "'");
            }
            if(userOrder.getCity() != null && !"城市".equals(userOrder.getCity())){
                sql.append(" AND custom.city = '" + userOrder.getCity() + "'");
            }
            if(userOrder.getProvince() != null && !"省份".equals(userOrder.getProvince())){
                sql.append(" AND custom.province = '" + userOrder.getProvince() + "'");
            }
            if(null != userOrder.getCustomName()){
                sql.append(" AND custom.cn_name like '%" + userOrder.getCustomName() + "%'");
            }
            if(null != userOrder.getBusinessName()){
                sql.append(" AND business.cn_name like '%" + userOrder.getBusinessName() + "%'");
            }
            if(null != userOrder.getStart()){
                sql.append(" AND UNIX_TIMESTAMP(r.evaluation_time) >=" + userOrder.getStart());
            }
            if(null != userOrder.getEnd()){
                sql.append(" AND UNIX_TIMESTAMP(r.evaluation_time) <=" + userOrder.getEnd());
            }
            if(null != userOrder.getUserStarStart()){
                sql.append(" AND r.user_stars >=" + userOrder.getUserStarStart());
            }
            if(null != userOrder.getUserStarEnd()){
                sql.append(" AND r.user_stars <=" + userOrder.getUserStarEnd());
            }
            if(null != userOrder.getId()){
                sql.append(" AND r.id =" + userOrder.getId());
            }
            if(null != userOrder.getUserId()){
                sql.append(" AND business.id =" + userOrder.getUserId());
            }
            if(null != userOrder.getPhone()){
                if(userOrder.getPhone().contains(",")){
                    sql.append(" AND LOCATE(custom.username, '" + userOrder.getPhone() + "')");
                }else{
                    sql.append(" AND custom.username =" + userOrder.getPhone());
                }

                sql.append(" AND r.user_stars != 0");
            }
        }
        if(findFlag == 1){
            sql.append( " order by r.evaluation_time desc " + " LIMIT " + pageStart + "," + pageOffset);
        }
        return sql.toString();
    }

    /**
     * 案例下推荐分页
     * @param para
     * @return
     */
    public String selectByUserRecommendBvoListByPage(Map<String, Object> para){
        StringBuffer sql = new StringBuffer();
//        sql.append("select r.id,c.`name` as channelName,r.sort,u.cn_name,u.avatar,u.biz_deal_count,u.biz_price from by_user_recommend r left join user u on u.username = r.userName" +
//                " left join by_channel c on c.id = u.biz_work_type where 1 = 1" );
        sql.append("select r.id,IFNULL(c.`name`,'') as channelName,r.sort,r.recommend_id as user_id,u.id as userId,u.cn_name,u.avatar,IFNULL(concat ( left( (select count(*) from by_example_discuss d where d.example_id = r.example_id)/" +
                " (select count(*) from by_example_discuss d where d.example_id = r.example_id and d.score > 3) *100,5),'%'),'100%') as perD," +
                " (select count(*) from by_user_order o where u.id = o.biz_user_id and o.status = 2) as biz_deal_count ," +
                " IFNULL((select min(p.current_price) from by_user_biz_product p where u.id = p.user_id and p.status = 1),0) as biz_price" +
                " from by_user_recommend r left join user u on u.username = r.userName left join by_channel c on c.id = u.biz_work_type where 1 = 1");

        int pageStart = 1;
        int pageOffset = 10;
        if(null != para.get("page")){
            RequestPage page = (RequestPage)para.get("page");
            pageStart = page.getStart();
            pageOffset = page.getPageSize();
        }
        if(null != para.get("userId")){
            sql.append(" AND r.user_id = " + para.get("userId"));
        }
        if(null != para.get("exampleId")){
            sql.append(" AND r.example_id = " + para.get("exampleId"));
        }
        sql.append( " order by r.id desc " + " LIMIT " + pageStart + "," + pageOffset);
        return sql.toString();
    }

    public String countByUserRecommendBvoListByPage(Map<String, Object> para){
        String sql = selectByUserRecommendBvoListByPage(para);
        return "select count(*) from (" + sql.split("LIMIT")[0] + ") temp";
    }

    /**
     * 分页查询婚礼预算
     * @param para
     * @return
     */
    public String selectExampleBudgetListByPage(Map<String, Object> para){
        StringBuffer sql = new StringBuffer();
        sql.append("select id, example_id, name, level, descn, material,unit,price,money,num, create_time, update_time from by_example_budget where 1 = 1" );

        int pageStart = 1;
        int pageOffset = 10;
        if(null != para.get("page")){
            RequestPage page = (RequestPage)para.get("page");
            pageStart = page.getStart();
            pageOffset = page.getPageSize();
        }

        if(null != para.get("exampleId")){
            sql.append(" AND example_id = " + para.get("exampleId"));
        }

        sql.append( " order by id desc " + " LIMIT " + pageStart + "," + pageOffset);
        return sql.toString();
    }

    public String countExampleBudgetListByPage(Map<String, Object> para){
        String sql = selectExampleBudgetListByPage(para);
        return "select count(*) from (" + sql.split("LIMIT")[0] + ") temp";
    }

    /**
     * 分页查询案例评论
     * @param para
     * @return
     */
    public String selectExampleDiscussListByPage(Map<String, Object> para){
        StringBuffer sql = new StringBuffer();
        sql.append("select id, example_id, name, imgUrl, content, score, create_time, update_time from by_example_discuss where 1 = 1" );

        int pageStart = 1;
        int pageOffset = 10;
        if(null != para.get("page")){
            RequestPage page = (RequestPage)para.get("page");
            pageStart = page.getStart();
            pageOffset = page.getPageSize();
        }

        if(null != para.get("exampleId")){
            sql.append(" AND example_id = " + para.get("exampleId"));
        }

        sql.append( " order by id desc " + " LIMIT " + pageStart + "," + pageOffset);
        return sql.toString();
    }

    public String countExampleDiscussListByPage(Map<String, Object> para){
        String sql = selectExampleDiscussListByPage(para);
        return "select count(*) from (" + sql.split("LIMIT")[0] + ") temp";
    }
    public String selectByUserBizAuthListPageSql(Map<String, Object> params){
        Integer findFlag = 1;
        int pageStart = 1;
        int pageOffset = 10;
        StringBuffer sql = new StringBuffer();
        ByUserBizAuthvo userBizAuthBvo = null;
        if(params.containsKey("auth") && params.get("auth") != null) {
            userBizAuthBvo = (ByUserBizAuthvo) params.get("auth");
            findFlag = userBizAuthBvo.getFindFlag();
        }
        if(findFlag == 1){
            if(userBizAuthBvo.getStatus() == 1){
                sql.append("SELECT a.id,a.img_url as imgUrl,a.`name`,a.certificatesType,a.`status`,DATE_FORMAT(a.update_time,'%Y-%m-%d %T') as createTimeName,a.descn, u.cn_name as userCnName,d.cn_name as dailishang,u.username as userMobile,GROUP_CONCAT((case a.auth_type when 1 then '平台认证' when 2 then '诚信认证' when 3 then '学院认证' end) SEPARATOR \";\") as authTypeName ,u.province,u.city,u.area as district,a.authLevel ");
            }else if(userBizAuthBvo.getStatus() == -1 || userBizAuthBvo.getStatus() == 2){
                sql.append("SELECT a.id,a.img_url as imgUrl,a.`name`,a.certificatesType,a.`status`,DATE_FORMAT(a.update_time,'%Y-%m-%d %T') as createTimeName,a.descn, u.cn_name as userCnName,d.cn_name as dailishang,u.username as userMobile,case a.auth_type when 1 then '平台认证' when 2 then '诚信认证' when 3 then '学院认证' end as authTypeName,u.province,u.city,u.area as district,a.authLevel ");
            }
        }else if(findFlag == 2){
            if(userBizAuthBvo.getStatus() == 1){
                sql.append("SELECT count(*) from (SELECT  a.* ");
            }else if(userBizAuthBvo.getStatus() == -1 || userBizAuthBvo.getStatus() == 2){
                sql.append("SELECT count(*) ");
            }
        }
        sql.append(" FROM by_user_biz_auth a LEFT JOIN `user` u ON a.user_id=u.id LEFT JOIN `user` d ON u.biz_agent_id = d.id where 1 =1 ");
        if(userBizAuthBvo != null){
            if(findFlag ==1) {
                if(null != params.get("start")){
                    pageStart = (int)params.get("start");
                }
                if(null != params.get("offset")){
                    pageOffset = (int)params.get("offset");
                }
            }
            if(userBizAuthBvo.getDistrict() != null && !"区域".equals(userBizAuthBvo.getDistrict())){
                sql.append(" AND u.area = '" + userBizAuthBvo.getDistrict() + "'");
            }
            if(userBizAuthBvo.getCity() != null && !"城市".equals(userBizAuthBvo.getCity())){
                sql.append(" AND u.city = '" + userBizAuthBvo.getCity() + "'");
            }
            if(userBizAuthBvo.getProvince() != null && !"省份".equals(userBizAuthBvo.getProvince())){
                sql.append(" AND u.province = '" + userBizAuthBvo.getProvince() + "'");
            }
            if(userBizAuthBvo.getUserCnName() != null){
                sql.append(" AND u.cn_name like '%" + userBizAuthBvo.getUserCnName() + "%'");
            }
            if(null != userBizAuthBvo.getUserMobile()){
                sql.append(" AND u.username like '%" + userBizAuthBvo.getUserMobile() + "%'");
            }
            if(null != userBizAuthBvo.getUserId()){
                sql.append(" AND a.user_id=" + userBizAuthBvo.getUserId());
            }
            if(null != userBizAuthBvo.getStatus()){
                sql.append(" AND a.status =" + userBizAuthBvo.getStatus());
            }
            if(null != userBizAuthBvo.getDailishang()){
                sql.append(" AND d.cn_name like '%" + userBizAuthBvo.getDailishang() + "%'");
            }
            if(null != userBizAuthBvo.getStart()){
                sql.append(" AND UNIX_TIMESTAMP(a.update_time) >=" + userBizAuthBvo.getStart());
            }
            if(null != userBizAuthBvo.getEnd()){
                sql.append(" AND UNIX_TIMESTAMP(a.update_time) <=" + userBizAuthBvo.getEnd());
            }
            if(null != userBizAuthBvo.getAuthLevel()){
                sql.append(" AND a.authLevel =" + userBizAuthBvo.getAuthLevel());
            }
            if(null != userBizAuthBvo.getAuthType()){
                sql.append(" AND a.auth_type =" + userBizAuthBvo.getAuthType());
            }
        }
        if(userBizAuthBvo.getStatus() == 1){
            sql.append(" GROUP BY a.user_id ");
        }
        if(findFlag == 1){
            sql.append( " ORDER BY a.update_time DESC " + " LIMIT " + pageStart + "," + pageOffset);
        }else {
            if(userBizAuthBvo.getStatus() == 1){
                sql.append(") y");
            }
        }
        return sql.toString();
    }

    /**
     * 字典表分页
     * @param para
     * @return
     */
    public String selectDictListByPage(Map<String, Object> para){
        StringBuffer sql = new StringBuffer();
        sql.append("select * from dict where 1 = 1 ");
        int pageStart = 1;
        int pageOffset = 10;
        if(null != para.get("page")){
            RequestPage page = (RequestPage)para.get("page");
            pageStart = page.getStart();
            pageOffset = page.getPageSize();
        }

        if(null != para.get("type")){
            sql.append(" AND type = " + para.get("type"));
        }
        sql.append(" order by id desc " + " LIMIT " + pageStart + "," + pageOffset);
        return sql.toString();
    }

    public String countDictListByPage(Map<String, Object> para){
        String sql = selectDictListByPage(para);
        return "select count(*) from (" + sql.split("LIMIT")[0] + ") temp";
    }

    /**
     * 栏目管理
     * @param params
     * @return
     */
    public String selectByChannelPageSql(Map<String, Object> params) {
        Integer findFlag = 1;
        int pageStart = 1;
        int pageOffset = 10;
        StringBuffer sql = new StringBuffer();
        if(params.containsKey("byChannel") && params.get("byChannel") != null) {
            ByChannel byChannel = (ByChannel) params.get("byChannel");
            findFlag = byChannel.getFindFlag();
        }
        if(findFlag == 1){
            sql.append("SELECT c.id, c.position_id, c.img_url, c.`name`, c.descn, c.`status`, c.create_time, c.update_time, c.sort, c1.`name` as parentName, c.url,c.`level` ");
        }else if(findFlag == 2){
            sql.append("SELECT count(*) ");
        }
        sql.append("FROM by_channel c LEFT JOIN by_channel c1 on c.parent_id=c1.id where 1=1 ");
        if(params.containsKey("byChannel") && params.get("byChannel") != null){
            if(findFlag ==1) {
                if(null != params.get("start")){
                    pageStart = (int)params.get("start");
                }
                if(null != params.get("offset")){
                    pageOffset = (int)params.get("offset");
                }
            }
            ByChannel byChannel = (ByChannel) params.get("byChannel");
            if(null != byChannel.getParentId()){
                sql.append(" AND c.parent_id =" + byChannel.getParentId());
            }
        }
        if(findFlag == 1){
            sql.append( " ORDER BY c.sort asc " + " LIMIT " + pageStart + "," + pageOffset);
        }
        return sql.toString();
    }

    /**
     * 分页查询用户消息
     * @param para
     * @return
     */
    public String selectByUserMessageListByPage(Map<String, Object> para){
        StringBuffer sql = new StringBuffer();
        sql.append("select * from by_user_message where 1 = 1" );

        int pageStart = 1;
        int pageOffset = 10;
        if(null != para.get("page")){
            RequestPage page = (RequestPage)para.get("page");
            pageStart = page.getStart();
            pageOffset = page.getPageSize();
        }

        if(null != para.get("userId")){
            sql.append(" AND user_id = " + para.get("userId"));
        }

        sql.append( " order by id desc " + " LIMIT " + pageStart + "," + pageOffset);
        return sql.toString();
    }

    public String countByUserMessageListByPage(Map<String, Object> para){
        String sql = selectByUserMessageListByPage(para);
        return "select count(*) from (" + sql.split("LIMIT")[0] + ") temp";
    }
}
